-- This is the main demo script for the session "Watch Out for Permission
-- Hijacking". Run this script logged in as sysadmin. Switch to other files,
-- when cues are given.

-- First create a demo database and login to be the application admin.
USE tempdb
go
DROP DATABASE IF EXISTS AppDB
CREATE DATABASE AppDB
ALTER AUTHORIZATION ON DATABASE::AppDB TO sa
go
CREATE LOGIN DebbieOwner WITH PASSWORD = 'StrrOOn6p@swörd'
go
USE AppDB
go
CREATE USER DebbieOwner
ALTER ROLE db_owner ADD MEMBER DebbieOwner
go

-- Switch to Debbie.sql

-- The server DBA runs some token database maintenance.
DBCC CHECKDB(AppDB) WITH NO_INFOMSGS
BACKUP DATABASE AppDB TO DISK = 'C:\temp\AppDB.bak' WITH INIT
ALTER INDEX ALL ON TableOne REBUILD
-- UPDATE STATISTICS TableOne
go
-- Check who are members of sysadmin?
SELECT name FROM sys.server_principals
WHERE is_srvrolemember('sysadmin', name) = 1
go

-- Hm, what is Debbie up to now? Switch to Debbie.sql

-- Run the DB maintenance again. Run the first two statements and 
-- check sysadmin again.
DBCC CHECKDB(AppDB) WITH NO_INFOMSGS
BACKUP DATABASE AppDB TO DISK = 'C:\temp\AppDB.bak' WITH INIT
SELECT name FROM sys.server_principals
WHERE is_srvrolemember('sysadmin', name) = 1
go
-- Now run the index rebuild and check again.
ALTER INDEX ALL ON TableOne REBUILD
SELECT name FROM sys.server_principals
WHERE is_srvrolemember('sysadmin', name) = 1
go
-- That's bad! Take DebbieOwner out of sysadmin.
ALTER SERVER ROLE sysadmin DROP MEMBER DebbieOwner
SELECT name FROM sys.server_principals
WHERE is_srvrolemember('sysadmin', name) = 1
go


-- Also test statistics update.
UPDATE STATISTICS TableOne
SELECT name FROM sys.server_principals
WHERE is_srvrolemember('sysadmin', name) = 1
go

-- That also fired the DDL trigger. Remove DebbieOwner again.
ALTER SERVER ROLE sysadmin DROP MEMBER DebbieOwner
SELECT name FROM sys.server_principals
WHERE is_srvrolemember('sysadmin', name) = 1
go

-- The remedy is to impersonate dbo.
EXECUTE AS USER = 'dbo'
go
ALTER INDEX ALL ON TableOne REBUILD
UPDATE STATISTICS TableOne
go
REVERT  -- Stops impersonation.
go
SELECT name FROM sys.server_principals
WHERE is_srvrolemember('sysadmin', name) = 1
go

-- But why did this work out? Let's look at our tokens.
SELECT type, usage, name FROM sys.user_token
SELECT type, usage, name FROM sys.login_token
go
-- And look at them when impersonating dbo.
EXECUTE AS USER = 'dbo'
SELECT type, usage, name FROM sys.user_token
SELECT type, usage, name FROM sys.login_token
go
REVERT

-- That is, when impersonating a datbase user, we are sandbox into the 
-- current database and lose all our server permissions.

-- Could Debbie counterstrike with help of REVERT?
-- Switch to Debbie.sql again.

-- Run index maintenance again:
EXECUTE AS USER = 'dbo'
go
ALTER INDEX ALL ON TableOne REBUILD
UPDATE STATISTICS TableOne
go
REVERT
go
SELECT name FROM sys.server_principals
WHERE is_srvrolemember('sysadmin', name) = 1
go
-- No, a REVERT only works against impersonation in the same scope.

-- Let's drop Debbie's evil trigger before we continue.
DROP TRIGGER EvilDdlTri ON DATABASE
SELECT * FROM sys.triggers

-- End of Demo 1.
------------------------------------------------------------
-- Demo2 - A Small Hiccup.
USE tempdb
go

-- Restore this database (backup is in download material)
RESTORE DATABASE AnotherDB FROM DISK = 'C:\Temp\AnotherDB.bak'
WITH MOVE 'AnotherDB'     TO 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Data\AnotherDB.mdf',
     MOVE 'AnotherDB_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Data\AnotherDB.ldf'
go

-- Move to this database and try to impersonate dbo.
USE AnotherDB
go
EXECUTE AS USER = 'dbo'
go
REVERT
go

-- This fails. Here is the reason: the owner_sid in sys.databases does
-- not match dbo in the database.
SELECT owner_sid FROM sys.databases WHERE name = db_name()
UNION ALL
SELECT sid FROM sys.database_principals WHERE name = 'dbo'

go
-- Remedy is to change the owner. And while we're at it, let's follow best
-- practice and create an SQL login which exists solely to own that database.
DECLARE @sql nvarchar(MAX)
SELECT @sql = concat(
     'CREATE LOGIN AnotherDB$owner WITH PASSWORD = ''', newid(), '''')
EXEC(@sql)
ALTER LOGIN AnotherDB$owner DISABLE
ALTER AUTHORIZATION ON DATABASE::AnotherDB TO AnotherDB$owner
go
-- Try to impersonate dbo again.
EXECUTE AS USER = 'dbo'
go
REVERT

-- End of demo 2.
---------------------------------------------------------------------
-- Demo 3.

USE master
go
-- Create a login for DevDave, a developer.
CREATE LOGIN DevDave WITH PASSWORD = 'Pässwort123&9'
go
USE AppDB
go
-- Create a database user for DevDave and add him to db_ddladmin.
-- Also, give him rights to run stored procedures and full access
-- to tables. (Because else, what's the point?)
CREATE USER DevDave
ALTER ROLE db_ddladmin ADD MEMBER DevDave
GRANT EXECUTE TO DevDave
GRANT SELECT, INSERT, UPDATE, DELETE TO DevDave
go

-- Switch to file DevDave.sql.

---------------------------------------------------------
-- Cleanup.
-- This script drops databases, logins and certificates created in the
-- other scripts.
USE master
go
IF db_id('AppDB') IS NOT NULL
BEGIN
   ALTER DATABASE AppDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
   DROP DATABASE AppDB
END
go
IF db_id('AnotherDB') IS NOT NULL
BEGIN
   ALTER DATABASE AnotherDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
   DROP DATABASE AnotherDB
END
go
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = 'DebbieOwner')
   DROP LOGIN DebbieOwner
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = 'DevDave')
   DROP LOGIN DevDave
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = 'AnotherDB$owner')
   DROP LOGIN AnotherDB$owner

